package com.example.spsaveqqty.userManage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.spsaveqqty.guides.Guide;
import com.example.spsaveqqty.travelguidesdatabase.DatabaseHelper;

import java.util.ArrayList;
import java.util.List;

public class UserDao {
    private DatabaseHelper mHelper;
    private SQLiteDatabase db;
    User user;
    Guide guide;

    public UserDao(Context context){
        mHelper = new DatabaseHelper(context);
    }
    //根据username查出user信息
    public User findUserByUserName(String username){
        user = new User();
        db = mHelper.getReadableDatabase();
        //根据username的值 从数据库中查出最新的user信息
        Cursor cursor = db.rawQuery("select * from user where username=?", new String[]{username});
        while (cursor.moveToNext()) {
            user.setUsername(username);
            user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
            user.setLocation(cursor.getString(cursor.getColumnIndex("location")));
            user.setSlogan(cursor.getString(cursor.getColumnIndex("slogan")));
            user.setSex(cursor.getInt(cursor.getColumnIndex("sex")));
            user.setBirthday(cursor.getString(cursor.getColumnIndex("birthday")));
            //获取头像：
            user.setFlag(cursor.getString(cursor.getColumnIndex("flag")));
            user.setPicture(cursor.getString(cursor.getColumnIndex("picture")));
        }
        cursor.close();
        db.close();
        return user;
    }
    //根据username和Passsword查出user个数
    public int getUserCountByUsernamePassword(String username,String password){
        user = new User();
        db = mHelper.getReadableDatabase();
        int count = 0;
        Cursor cursor = db.rawQuery("select * from user where username=? and password=?",new String[]{username,password});
        count = cursor.getCount();
        cursor.close();
        db.close();
        return count;
    }
    //根据username查出user个数
    public int getUserCount(String username){
        user = new User();
        db = mHelper.getReadableDatabase();
        int count = 0;
        Cursor cursor = db.rawQuery("select * from user where username=?",new String[]{username});
        count = cursor.getCount();
        cursor.close();
        db.close();
        return count;
    }
    //根据username查出user的id：
    public int getUserIdByUsername(String username){
        int id = 0;
        //user = new User();
        db = mHelper.getReadableDatabase();
        //根据username的值 从数据库中查出最新的user信息
        Cursor cursor = db.rawQuery("select * from user where username=?", new String[]{username});
        while (cursor.moveToNext()) {
            id = cursor.getInt(cursor.getColumnIndex("id"));
        }
        cursor.close();
        db.close();
        return id;
    }
    //根据username查出头像：
    public String getUserPictureByUsername(String username){
        String picture = "";
        db = mHelper.getReadableDatabase();
        //根据username的值 从数据库中查出最新的user信息
        Cursor cursor = db.rawQuery("select * from user where username=?", new String[]{username});
        while (cursor.moveToNext()) {
            picture = cursor.getString(cursor.getColumnIndex("picture"));
        }
        cursor.close();
        db.close();
        return picture;
    }
    //根据username查出头像flag：
    public String getUserPictureFlagByUsername(String username){
        String flag = "";
        db = mHelper.getReadableDatabase();
        //根据username的值 从数据库中查出最新的user信息
        Cursor cursor = db.rawQuery("select * from user where username=?", new String[]{username});
        while (cursor.moveToNext()) {
            flag = cursor.getString(cursor.getColumnIndex("flag"));
        }
        cursor.close();
        db.close();
        return flag;
    }
    //添加用户
    public void insert(User user){
        db = mHelper.getWritableDatabase();
        ContentValues values;
        values = new ContentValues();
        values.put("username",user.getUsername());
        values.put("password",user.getPassword());
        db.insert("user",null,values);
        db.close();
    }
    //修改用户信息
    public void updateUserInfo(ContentValues values,String username){
        db = mHelper.getWritableDatabase();
        db.update("user", values, "username=?", new String[]{username});
        db.close();
    }
    //获取个人旅游攻略：
    public List<Guide> findPersonalGuidesByUsername(int userID){
        List<Guide> guidesList = new ArrayList<Guide>();
        db = mHelper.getReadableDatabase();
        String sql = "select user.*,guide.*,attraction.attrac_name from user,guide,attraction " +
                "where user.id="+userID+" "+
                "and guide.producer_id=user.id"+" "+
                "and guide.place_id=attraction.attrac_id";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            guide = new Guide();
            guide.setIconFlag(cursor.getString(cursor.getColumnIndex("flag")));
            guide.setProducer_image(cursor.getString(cursor.getColumnIndex("picture")));
            guide.setProducer_name(cursor.getString(cursor.getColumnIndex("username")));
            guide.setConent(cursor.getString(cursor.getColumnIndex("conent")));
            guide.setTitle(cursor.getString(cursor.getColumnIndex("title")));
            guide.setDatetime(cursor.getString(cursor.getColumnIndex("datetime")));
            String [] pId = cursor.getString(cursor.getColumnIndex("picture_id")).split(",");
            String [] picture = new String[pId.length];
            for(int i=0;i<pId.length;i++){
                SQLiteDatabase db2 =  mHelper.getReadableDatabase();;
                Cursor cur = db2.rawQuery("select url from picture where id="+pId[i],null);
                //if(cur!=null) cur.moveToFirst();
                while (cur.moveToNext()){
                    picture [i] = cur.getString(cur.getColumnIndex("url"));
                }
                cur.close();
                db2.close();
            }
            guide.setPicture(picture);
            guide.setPlace_name(cursor.getString(cursor.getColumnIndex("attrac_name")));
            guide.setStrategy_id(cursor.getInt(cursor.getColumnIndex("strategy_id")));
            guidesList.add(guide);
        }
        cursor.close();
        db.close();
        return  guidesList;
    }
    //删除个人旅游攻略：
    public void delPersonalGuide(int strategy_id){
        db = mHelper.getWritableDatabase();
        String deleteSQL = "delete from guide where strategy_id = "+strategy_id;
        db.execSQL(deleteSQL);
        db.close();
    }
}
